package bbs2.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.http.HttpSession;

import bbs2.model.Article;
import bbs2.service.UserService;

public class ArticleDAO {

    private static Article makeArticle(ResultSet rs, boolean includeBody) throws Exception {
        Article article = new Article();
        article.setId(rs.getInt("ID"));
        article.setNo(rs.getInt("no"));
        article.setUserId(rs.getInt("userID"));
        article.setName(rs.getString("name"));
        article.setWriteTime(rs.getTimestamp("writeTime"));
        article.setTitle(rs.getString("title"));
        article.setCategory(rs.getString("category"));
        if (includeBody) { 
            article.setBoardId(rs.getInt("boardID"));
            article.setCategoryId(rs.getInt("categoryID"));
            article.setBody(rs.getString("body"));
            article.setNotice(rs.getBoolean("notice"));
            article.setParentId(rs.getInt("parentID"));
            article.setDistance(rs.getInt("distance"));
        } else {
            article.setIndentation(rs.getInt("indentation"));
            article.setTotalReadCount(rs.getInt("totalReadCount"));
            article.setMyReadCount(rs.getInt("myReadCount"));
            article.setCommentCount(rs.getInt("commentCount"));
        }
        return article;
    }
    
    public static ArrayList<Article> selectPage(int boardId, int userId, int currentPage, int pageSize, int srchType, String srchText, int categoryId) throws Exception {
        ArrayList<Article> list = new ArrayList<Article>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "EXEC articleSelectPage ?, ?, ?, ?, ?, ?, ?"; 
            statement = connection.prepareStatement(sql);            
            statement.setInt(1, boardId);
            statement.setInt(2, userId);
            statement.setInt(3, currentPage);
            statement.setInt(4, pageSize);
            statement.setInt(5, srchType);
            statement.setString(6, srchText);
            statement.setInt(7, categoryId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) 
                list.add(makeArticle(resultSet, false));
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return list;
    }

    public static ArrayList<Article> selectNotice(int boardId, int userId) throws Exception {
        ArrayList<Article> list = new ArrayList<Article>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "EXEC articleSelectNotice ?, ?"; 
            statement = connection.prepareStatement(sql);            
            statement.setInt(1, boardId);
            statement.setInt(2, userId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) 
                list.add(makeArticle(resultSet, false));
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return list;
    }
    
    public static int getRecordCount(int boardId, int srchType, String srchText, int category) throws Exception {
        int count = 0;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "EXEC articleRecordCount ?, ?, ?, ?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, boardId);
            statement.setInt(2, srchType);
            statement.setString(3, srchText);
            statement.setInt(4, category);
            resultSet = statement.executeQuery();
            if (resultSet.next()) 
                count = resultSet.getInt(1);
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return count;
    }
    
    public static Article selectById(int id, int userID) throws Exception {
        Article article = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;        
        try {
            connection = DB.getConnection();
            String sql = "articleSelectById ?, ?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.setInt(2, userID);
            resultSet = statement.executeQuery();
            if (resultSet.next()) article = makeArticle(resultSet, true);
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return article;
    }
    
    public static void create(int boardId, int categoryId, String title, String body, boolean notice, HttpSession session) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "EXEC articleCreate ?, ?, ?, ?, ?, ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, boardId);
            statement.setInt(2, UserService.getCurrentUser(session).getId());
            statement.setInt(3, categoryId);
            statement.setString(4, title);
            statement.setString(5, body);
            statement.setBoolean(6, notice);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }    

    public static void createReply(int boardId, int articleId, int categoryId, String title, String body, boolean notice, HttpSession session) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "EXEC articleCreateReply ?, ?, ?, ?, ?, ?, ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, boardId);
            statement.setInt(2, articleId);
            statement.setInt(3, UserService.getCurrentUser(session).getId());
            statement.setInt(4, categoryId);
            statement.setString(5, title);
            statement.setString(6, body);
            statement.setBoolean(7, notice);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }    
    
    public static void update(Article article) throws Exception {
        update(article.getId(), article.getCategoryId(), article.getTitle(), article.getBody(), article.isNotice());
    }

    public static void update(int articleId, int categoryId, String title, String body, boolean notice) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "UPDATE [Article] SET [categoryId]=?, [title]=?, [body]=?, [notice]=? WHERE [id]=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, categoryId);
            statement.setString(2, title);
            statement.setString(3, body);
            statement.setBoolean(4, notice);
            statement.setInt(5, articleId);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }    

    public static void deleteById(int articleId) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "articleDeleteById ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, articleId);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
    }   

}
